import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.*;

/**
 * <p>
 * 创建日期：2021-07-28 09:33
 *
 * @author Hoppinging
 * @version 1.1
 * @since 1.0
 */
class Cat{
    int id;
    String name;
    double weight;
    Date birthday;

    public Cat(int id, String name, double weight, Date birthday) {
        this.id = id;
        this.name = name;
        this.weight = weight;
        this.birthday = birthday;
    }
}
public class JDBC {
    static Connection connection = null;
    static PreparedStatement preparedStatement = null;
    static ResultSet resultSet = null;
    static {
        BasicDataSource source = new BasicDataSource();
        source.setDriverClassName("com.mysql.cj.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306/pet?useUnicode=true&characterEncoding=UTF-8&userSSL=true&serverTimezone=GMT%2B8");
        source.setUsername("root");
        source.setPassword("112159");
        source.setInitialSize(20);
        source.setMaxTotal(80);
        source.setMaxIdle(40);
        source.setMinIdle(20);
        source.setMaxWaitMillis(6000);
        try {
            connection = source.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void release(){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public int add(Cat cat){
        switch (find(cat.id)){
            case 0 -> {
                //系统错误
                return 0;
            }
            case 1 -> {
                //已有ID
                return 1;
            }
            case 2 -> {
                try{
                    String sql = "insert into cat(ID,Name,Weight,Birthday) values(?,?,?,?)";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setInt(1,cat.id);
                    preparedStatement.setString(2,cat.name);
                    preparedStatement.setDouble(3,cat.weight);
                    preparedStatement.setDate(4,cat.birthday);
                    int num = preparedStatement.executeUpdate();
                    if(num>0){
                        //插入成功
                        return 2;
                    }else {
                        //插入失败
                        return 3;
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    //系统错误
                    return 0;
                }
            }
        }
        return 0;
    }

    public int remove(int ID){
        switch (find(ID)){
            case 0 -> {
                //系统错误
                return 0;
            }
            case 1 -> {
                try{
                    String sql = "delete from cat where ID=?";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setInt(1,ID);
                    int num = preparedStatement.executeUpdate();
                    if(num>0){
                        //删除成功
                        return 1;
                    }else {
                        //删除失败
                        return 2;
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    return 0;
                }
            }
            case 2 -> {
                //无此ID
                return 3;
            }
        }
        return 0;
    }

    public int change(int ID, int new_ID, String name, double weight, Date birthday){
        switch (find(ID)){
            case 0 -> {
                //系统错误
                return 0;
            }
            case 1 -> {
                try{
                    String sql = "update cat set ID=?,Name=?,Weight=?,Birthday=? where ID=?";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setInt(1,new_ID);
                    preparedStatement.setString(2,name);
                    preparedStatement.setDouble(3,weight);
                    preparedStatement.setDate(4,birthday);
                    preparedStatement.setInt(5,ID);
                    int num = preparedStatement.executeUpdate();
                    if(num>0){
                        //修改成功
                        System.out.println("=============================================");
                        System.out.println("new cat info\nID:" + new_ID + "\nName:" + name + "\nWeight:" + weight + "\nBirthday:" + birthday);
                        return 1;
                    }else {
                        //修改失败
                        return 2;
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    return 0;
                }
            }
            case 2 -> {
                //无此ID
                return 3;
            }
        }
        return 0;
    }

    public int find(int ID) {
        try{
            String sql = "select * from cat where ID=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, String.valueOf(ID));
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                //ID存在
                int cat_ID = resultSet.getInt(1);
                String cat_Name = resultSet.getString(2);
                double cat_Weight = resultSet.getDouble(3);
                Date cat_Birthday = resultSet.getDate(4);
                System.out.println("cat info\nID:" + cat_ID + "\nName:" + cat_Name + "\nWeight:" + cat_Weight + "\nBirthday:" + cat_Birthday);
                return 1;
            } else {
                //ID不存在
                return 2;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            //查询失败
            return 0;
        }
    }
    public int find(String name) {
        try{
            String sql = "select * from cat where Name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                //Name存在
                int cat_ID = resultSet.getInt(1);
                String cat_Name = resultSet.getString(2);
                double cat_Weight = resultSet.getDouble(3);
                Date cat_Birthday = resultSet.getDate(4);
                System.out.println("cat info\nID:" + cat_ID + "\nName:" + cat_Name + "\nWeight:" + cat_Weight + "\nBirthday:" + cat_Birthday);
                return 1;
            } else {
                //Name不存在
                return 2;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            //查询失败
            return 0;
        }
    }
    public int print() {
        try{
            String sql = "select * from cat";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int cat_ID = resultSet.getInt(1);
                String cat_Name = resultSet.getString(2);
                double cat_Weight = resultSet.getDouble(3);
                Date cat_Birthday = resultSet.getDate(4);
                System.out.println("ID:" + cat_ID + "\nName:" + cat_Name + "\nWeight:" + cat_Weight + "\nBirthday:" + cat_Birthday + "\n");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            //系统错误
            return 0;
        }
        //查询完成
        return 1;
    }
}
